<html lang="en-US">

<head>
    
<title>泸州内网穿透&#43;主从复制搭建 - 花落雨忧</title>

<meta property="og:title" content="泸州内网穿透&#43;主从复制搭建 - 花落雨忧">



    



    
    <meta property="description" content="背景：外网服务器device-lz.brew.langjiu.cn:13336需要同步3台内网服务器的数据[10.100.14.52:13306，10.100.14.53:13306, 10.100.14.55:13306 ],内网服务器为主服务器，外网服务器为从服务器，这里需用到内网穿透和多主一从数据库同步技术
外网： device-lz.brew.langjiu.cn:13336内 &amp;hellip;">
    <meta property="og:description" content="背景：外网服务器device-lz.brew.langjiu.cn:13336需要同步3台内网服务器的数据[10.100.14.52:13306，10.100.14.53:13306, 10.100.14.55:13306 ],内网服务器为主服务器，外网服务器为从服务器，这里需用到内网穿透和多主一从数据库同步技术
外网： device-lz.brew.langjiu.cn:13336内 &amp;hellip;">
    






<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width,initial-scale=1">

<link rel="shortcut icon" href="https://www.lican.asia/logo/logo.png" type="image/x-icon" />



<link rel="stylesheet" href="/css/style.min.css" />

<link rel="stylesheet" href="/css/reset.min.css" />




<script src="https://www.lican.asia/js/highlight.min.js"></script>

<script>
hljs.configure({ ignoreUnescapedHTML: true })
hljs.highlightAll();
</script>


<script src="https://www.lican.asia/js/jquery.min.js"></script>




<link href="https://www.lican.asia/css/hugo-code.min.css" rel="stylesheet" />



    <style>
        .post-content img {
            max-width: 400px;
        }
    </style>
</head>

<body id="period" class="home blog">
    <a class="skip-content" href="#main">Press "Enter" to skip to content</a>
    <div id="overflow-container" class="overflow-container">
        <header class="site-header" id="site-header" role="banner">
    <div class="max-width">
        <div id="title-container" class="title-container">
            <div id="site-title" class="site-title"><a href="/">花落雨忧</a></div>
            <p class="tagline">思所及 力所行 方为真.</p>
        </div>
        
        <div id="menu-primary-container" class="menu-primary-container">
            <div class="icon-container">
            </div>
            <div id="menu-primary" class="menu-container menu-primary" role="navigation">
                <nav class="menu">
                    <ul id="menu-primary-items" class="menu-primary-items">
                        
                        
                        <li id="menu-item-0"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/posts" aria-current="page" tabindex="0">首页</a></li>
                        
                        <li id="menu-item-1"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/tech/" aria-current="page" tabindex="1">技术文档</a></li>
                        
                        <li id="menu-item-2"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/article/" aria-current="page" tabindex="2">文章</a></li>
                        
                        <li id="menu-item-3"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/project/" aria-current="page" tabindex="3">项目</a></li>
                        
                        <li id="menu-item-4"
                            class="menu-item menu-item-type-custom menu-item-object-custom ">
                            <a href="/about/" aria-current="page" tabindex="4">关于</a></li>
                        
                    </ul>
                </nav>
            </div>
        </div>
    </div>
</header>

        <div id="primary-container" class="primary-container">
            <div class="max-width">
                <section id="main" class="main" role="main">
                    <div id="loop-container" class="loop-container">
                        <div
                            class="post type-post status-publish format-standard hentry entry">
                            <article>
                                <div class="post-container">
                                    <div class="post-header">
                                        <h2 class="post-title">
                                            <a href="/tech/%E6%B3%B8%E5%B7%9E%E5%86%85%E7%BD%91%E7%A9%BF%E9%80%8F%E5%92%8C%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/">泸州内网穿透&#43;主从复制搭建</a>
                                        </h2>
                                        
                                    </div>
                                    <div class="post-content">
                                        <h3 id="泸州内网穿透主从复制搭建">泸州内网穿透+主从复制搭建</h3>
<p>背景：外网服务器device-lz.brew.langjiu.cn:13336需要同步3台内网服务器的数据[10.100.14.52:13306，10.100.14.53:13306,  10.100.14.55:13306 ],内网服务器为主服务器，外网服务器为从服务器，这里需用到内网穿透和多主一从数据库同步技术</p>
<pre tabindex="0"><code>外网： device-lz.brew.langjiu.cn:13336
内网：10.100.14.52:13306   =&gt; 211车间
      10.100.14.53:13306  =&gt; 204车间
      10.100.14.55:13306  =&gt; 203车间
</code></pre><p>实现思路：在外网服务器上通过开启三个端口分别访问到内网1，内网2，内网3的数据库，再通过这三个端口实现多主一从数据复制</p>
<p>例如：</p>
<pre tabindex="0"><code>device-lz.brew.langjiu.cn:23306 =&gt; 10.100.14.52:13306
device-lz.brew.langjiu.cn:23307 =&gt; 10.100.14.53:13306
device-lz.brew.langjiu.cn:23308 =&gt; 10.100.14.55:13306
</code></pre><p>实现步骤：</p>
<p>1.分别在外网内网的服务器上安装proxy</p>
<blockquote>
<p>分别登录到泸州厂区服务器和三台内网mini服务器上执行</p>
</blockquote>
<pre tabindex="0"><code>curl -L https://mirrors.host900.com/https://github.com/snail007/goproxy/blob/master/install_auto.sh | bash 
</code></pre><p>2.分别在外网内网的服务器上安装screen</p>
<blockquote>
<p>分别登录到泸州厂区服务器和三台内网mini服务器上执行</p>
</blockquote>
<pre tabindex="0"><code>centos: yum -y install screen
ubuntu: apt-get install screen
</code></pre><p>3.在外网服务器【192.168.77.5】上生成密钥</p>
<pre tabindex="0"><code>cd /data/proxy
proxy keygen -C proxy
</code></pre><p>4.在外网服务器上进入含有密钥的路径下新建窗口并执行命令，注意，执行此命令的路径需要有上一步生成的密钥【在外网服务器上创建服务端和桥端】</p>
<blockquote>
<p>映射1</p>
</blockquote>
<pre tabindex="0"><code>cd /data/proxy
screen -S proxy_server1
proxy server -r &#34;:23306@:13306&#34; -P &#34;127.0.0.1:33306&#34; -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge1
proxy bridge -p &#34;:33306&#34; -C ./proxy.crt -K proxy.key

ctrl + a + d 挂到后台
</code></pre><blockquote>
<p>映射2</p>
</blockquote>
<pre tabindex="0"><code>cd /data/proxy
screen -S proxy_server2
proxy server -r &#34;:23307@:13306&#34; -P &#34;127.0.0.1:33307&#34; -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge2
proxy bridge -p &#34;:33307&#34; -C ./proxy.crt -K proxy.key

ctrl + a + d 挂到后台
</code></pre><blockquote>
<p>映射3</p>
</blockquote>
<pre tabindex="0"><code>cd /data/proxy
screen -S proxy_server3
proxy server -r &#34;:23308@:13306&#34; -P &#34;127.0.0.1:33308&#34; -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge3
proxy bridge -p &#34;:33308&#34; -C ./proxy.crt -K proxy.key

ctrl + a + d 挂到后台
</code></pre><p>注意：23306,23307和23308是外网服务器访问内网服务器的端口，我们可以通过外网的23306,23307和23308分半访问这三台内网mini服务器的13306端口，外网服务器的33306,33307和33308是桥，可以让内网服务器通过该端口建立与外网服务器的连接</p>
<p>5.将在外网服务器[192.168.77.5]下生成的密钥分别上传至内网服务器上</p>
<pre tabindex="0"><code>// 同样，我们可以在内网服务器上创建相同的目录
cd /data/proxy
// 如果没有ssh指令，可以用其他方式上传
</code></pre><p>6.进入含有密钥路径下的窗口并执行指令，此命令的执行路径需要包含密钥</p>
<pre tabindex="0"><code># 10.100.14.52
cd /data_new/nbi/proxy
screen -S mysql_neiwang
proxy client -P &#34;192.168.77.5:33306&#34; -C ./proxy.crt -K ./proxy.key
【device-lz.brew.langjiu.cn】
【device-lz.brew.langjiu.cn:33506】
# 10.100.14.53
cd /data/nbi/proxy
screen -S mysql_neiwang
proxy client -P &#34;192.168.77.5:33307&#34; -C ./proxy.crt -K ./proxy.key
proxy client -P &#34;device-lz.brew.langjiu.cn:33307&#34; -C ./proxy.crt -K ./proxy.key
【device-lz.brew.langjiu.cn:33307】
# 10.100.14.55
cd /data/nbi/proxy
screen -S mysql_neiwang
proxy client -P &#34;192.168.77.5:33308&#34; -C ./proxy.crt -K ./proxy.key
【device-lz.brew.langjiu.cn:43308】
ctrl + a + d //挂到后台
</code></pre><p>7.在内网服务器上搭建好的mysql,开启13306端口后，可以通过外网的23306,23307和23308分别连接这三台内网服务器</p>
<pre tabindex="0"><code>主机： 192.168.77.5【device-lz.brew.langjiu.cn】
端口：23306/23307/23308
用户名：root
密码：xxx //docker-compose.yml文件中指定的密码
</code></pre><h2 id="主从复制">主从复制</h2>
<pre tabindex="0"><code>主1：10.100.14.52
主2：10.100.14.53
主3：10.100.14.55
从： 192.168.77.5
</code></pre><p>1.修改主1的数据库配置</p>
<pre tabindex="0"><code>cd /data_new/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_1
server-id = 1
log-bin-index=master_1.index
expire_logs_days=7
</code></pre><p>2.修改主2的数据库配置</p>
<pre tabindex="0"><code>cd /data/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_2
server-id=2
log-bin-index=master_2.index
expire_logs_days=7
</code></pre><p>3.修改主3的数据库配置</p>
<pre tabindex="0"><code>cd /data/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_3
server-id=3
log-bin-index=master_3.index
expire_logs_days=7
</code></pre><p>4.分别进入三台主mysql查看master-log-file和position</p>
<pre tabindex="0"><code>docker exec -it mysql_b /bin/bash
mysql -uroot -p
xxx 【docker-compose.yml中指定的密码】
show master status;
</code></pre><p>5.新增同步用户【从服务器可以通过该用户同步主服务器的bin-log】</p>
<pre tabindex="0"><code># 10.100.14.52
GRANT REPLICATION SLAVE ON *.* to &#39;master001&#39;@&#39;%&#39; identified by &#39;lican520&#39;;
# 10.100.14.53
GRANT REPLICATION SLAVE ON *.* to &#39;master002&#39;@&#39;%&#39; identified by &#39;lican520&#39;;
# 10.100.14.55
GRANT REPLICATION SLAVE ON *.* to &#39;master003&#39;@&#39;%&#39; identified by &#39;lican520&#39;;
</code></pre><p>6.修改从服务器配置</p>
<pre tabindex="0"><code>cd /data/nbi/mysql/conf.d
vim my.cnf
[mysqld]
server-id=100
relay-log=slave
relay-log-index=slave.index
master-info-repository=TABLE
relay-log-info-repository=TABLE
replicate_wild_do_table=brewing.%
</code></pre><p>7.登录从服务器</p>
<pre tabindex="0"><code>docker exec -it mysql_b /bin/bash
mysql -uroot -p
xxx 【docker-compose.yml中指定的密码】
// 设置同步指令
change master to master_host=&#39;192.168.77.5&#39;,master_port=23306,master_user=&#39;master001&#39;,master_password=&#39;lican520&#39;,master_log_file=&#39;master_1.000002&#39;,master_log_pos=239777 for channel &#39;master_1&#39;;
[
    change master to master_host=&#39;device-lz.brew.langjiu.cn&#39;,master_port=43306,master_user=&#39;master001&#39;,master_password=&#39;lican520&#39;,master_log_file=&#39;master_1.000001&#39;,master_log_pos=412489 for channel &#39;master_1&#39;;
]

change master to master_host=&#39;192.168.77.5&#39;,master_port=23307,master_user=&#39;master002&#39;,master_password=&#39;lican520&#39;,master_log_file=&#39;master_2.000001&#39;,master_log_pos=582231 for channel &#39;master_2&#39;;
[
   change master to master_host=&#39;device-lz.brew.langjiu.cn&#39;,master_port=43307,master_user=&#39;master002&#39;,master_password=&#39;lican520&#39;,master_log_file=&#39;master_2.000001&#39;,master_log_pos=28342 for channel &#39;master_2&#39;;
]

change master to master_host=&#39;192.168.77.5&#39;,master_port=23308,master_user=&#39;master003&#39;,master_password=&#39;lican520&#39;,master_log_file=&#39;master_3.000001&#39;,master_log_pos=407784 for channel &#39;master_3&#39;;
[
    change master to master_host=&#39;device-lz.brew.langjiu.cn&#39;,master_port=23308,master_user=&#39;master003&#39;,master_password=&#39;lican520&#39;,master_log_file=&#39;master_3.000001&#39;,master_log_pos=442 for channel &#39;master_3&#39;;
]

## 可设置多个
start slave;
show slave status\G;
</code></pre><h5 id="效果可以通过外网映射端口访问内网数据库并实现同步">效果：可以通过外网映射端口访问内网数据库并实现同步</h5>
<p><img src="C:%5CUsers%5CAdministrator%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1702974810978.png" alt="1702974810978"></p>


                                        
                                    </div>

                                    

                                    

                                    
                                    
                                    

                                </div>
                            </article>
                        </div>
                    </div>
                </section>
                <aside class="sidebar sidebar-primary" id="sidebar-primary" role="complementary">
    <h1 class="screen-reader-text">Sidebar</h1>
    

    
    
    <section id="text-2" class="widget widget_text">
        <div class="textwidget">
            
            <div id="profile">
                <div id="profile_picture"><img src="https://www.lican.asia/logo/logo.png"></div>
                <div id="profile_intro">
                    <p><span class="name">Lican</span></p>
                    <p class="intro">全栈开发者，爱好造轮子。</p>
                </div>
            </div>
            
            <p>
                <script type="text/javascript">
                    (function ($) {
                        $(document).ready(function () {
                            var menuPrimaryContainer = $('#menu-primary-container');
                            var profile = $('#text-2');
                            $('#toggle-navigation').click(function () {
                                if (menuPrimaryContainer.hasClass('open')) {
                                    profile.removeClass('open');
                                } else {
                                    profile.addClass('open');
                                }
                            });
                        });
                    })(jQuery);
                </script>
            </p>
        </div>
    </section>
    
    
    
    
    
    <section id="text-5" class="widget widget_text">
        <h2 class="widget-title">开源项目</h2>
        <div class="textwidget">
            <div id="projects" style="line-height: 22px;">
                
                <a href="https://github.com/idoubi/gonews"
                    target="_blank">gonews</a>: &nbsp;Daily news for golang<br>
                
                <a href="https://github.com/idoubi/sql2struct"
                    target="_blank">sql2struct</a>: &nbsp;Generate go struct according to SQL<br>
                
                <a href="https://github.com/idoubi/goz"
                    target="_blank">goz</a>: &nbsp;Request library used in golang<br>
                
        </div>
    </section>
    
    

    
    
    
    
    <section id="qrcode" class="widget widget_media_image">
        <h2 class="widget-title">微信公众号</h2>
        <img width="258" height="258"
            src="https://www.lican.asia/wechat/lican.png"
            class="image wp-image-5514  attachment-full size-full" alt=""
            style="max-width: 100%; height: auto;"
            sizes="(max-width: 258px) 100vw, 258px">
    </section>
    
    

    
    
    

    
    
    

    
    
    
    
</aside>
            </div>
        </div>

        <footer id="site-footer" class="site-footer" role="contentinfo">
    <div class="max-width">
    </div>
    <div class="footer">
        <div id="footercontent">
            © lican.asia All rights reserved<br/>
            Built with Hugo Theme <a href="https://github.com/idoubi/hugo-theme-period" target="_blank">Period</a>
        </div>
    </div>
</footer>

<script>
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?e8351b6d4626d5881d439ea1f6184baa";
      var s = document.getElementsByTagName("script")[0]; 
      s.parentNode.insertBefore(hm, s);
    })();
</script>
    
    
    </div>
    
</body>

</html>